import pymysql

class DBHelper :
    #打开数据库
    def openDB(self) :
        #把局部变量变成全局变量
        global conn
        conn=pymysql.connect(
        host="192.168.174.137",
        user="root",
        password="root123456",
        database="test",
        port=3306,
        charset="utf8")

        global vehicle
        vehicle=conn.cursor()

    #关闭数据库
    def closeDB(self) :
        vehicle.close()
        conn.close()
    
    #编辑部分
    def edit(self,sql) :
        self.openDB()
        result=vehicle.execute(sql)
        conn.commit()
        self.closeDB
        return result
    
    #查找一条
    def selectOne(self,sql) :
        self.openDB()
        vehicle.execute(sql)
        result=vehicle.fetchone()
        self.closeDB()
        return result
    
    #查找多条
    def selectAll(self,sql) :
        self.openDB()
        vehicle.execute(sql)
        result=vehicle.fetchall()
        self.closeDB()
        return result

class MovieTypeDAL(DBHelper) :
    #录入电影类型信息
    def insert(self,mt) :
        sql=f"insert into MovieType values({mt.tid},'{mt.tname}','{mt.tcontent}',now())"
        return self.edit(sql)
    
    #修改电影类型信息
    def update(self,mt) :
        sql=f"update MovieType set tname='{mt.tname}',tcontent='{mt.tcontent}' where tid='{mt.tid}'"
        return self.edit(sql)
    
    #删除电影类型信息
    def delete(self,tid) :
        sql=f"delete from MovieType where tid={tid}"
        return self.edit(sql)
    
    #查询电影类型编号(单条)
    def selecttid(self,tid) :
        sql=f"select * from MovieType where tid='{tid}'"
        return self.selectOne(sql)

    #查询所有电影类型信息
    def select(self) :
        sql="select *,DATE_FORMAT(tdate,'%Y年%m月%d日') from MovieType"
        return self.selectAll(sql)
    
class MovieDAL(DBHelper) :
    #查询对应类型下所有电影(根据tid)
    def selectAllMovie(self,tid) :
        sql=f"""
        select id,title,mdate,score,tname from
        MovieType mt
        inner join
        Movie m on mt.tid=m.tid where mt.tid={tid}
            """
        return self.selectAll(sql)
    
    #添加一条新的电影信息
    def insert(self,m) :
        sql=f"insert into Movie values({m.id},'{m.title}','{m.mdate}',{m.score},{m.tid})"
        return self.edit(sql)
    
    #查询电影编号(单条)
    def selectid(self,id) :
        sql=f"select * from Movie where id={id}"
        return self.selectOne(sql)

    #修改一条电影的信息
    def updateMovie(self,m) :
        sql=f"update Movie set title='{m.title}',mdate='{m.mdate}',score={m.score},tid={m.tid} where id={m.id}"
        return self.edit(sql)
    
    #删除一条电影信息
    def deleteMovie(self,id) :
        sql=f"delete from Movie where id={id}"
        return self.edit(sql)
    
    #模糊查询
    def FuzzyQuery(self,title) :
        sql=f"""
            select id,title,mdate,score,tname from 
            MovieType mt
            inner join
            Movie m
            on mt.tid=m.tid where title like '%{title}%'
             """
        return self.selectAll(sql)